USE [IUDICIUM]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uf_GetInvolucradosList]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[uf_GetInvolucradosList]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[uf_GetInvolucradosList]
(
	@intServicioId	INT
)
RETURNS VARCHAR(500)
AS
BEGIN

	DECLARE @listStr VARCHAR(MAX)
	
	SELECT @listStr = COALESCE(@listStr+', ' ,'') + [nombre]
	FROM (
		SELECT pn.[nombre] AS [nombre]
		FROM	[dbo].[tbl_PersonaNatural]pn,
				[dbo].[tbl_Persona] p,
				[dbo].[tbl_Involucrado]i,
				[dbo].[tbl_Servicio] s		
				
		WHERE	s.ServicioId = @intServicioId
				AND
				s.ServicioId = i.ServicioId
				AND
				i.PersonaId=p.PersonaId
				AND
				p.PersonaId= pn.PersonaNaturalId
		UNION
		SELECT pj.[razonSocial] AS [nombre]
		FROM	[dbo].[tbl_PersonaJuridica] pj,
				[dbo].[tbl_Persona] p,
				[dbo].[tbl_Involucrado] i,
				[dbo].[tbl_Servicio] s		
				
		WHERE	s.ServicioId = @intServicioId
				AND
				s.ServicioId = i.ServicioId
				AND
				i.PersonaId=p.PersonaId
				AND
				p.PersonaId= pj.PersonaJuridicaId
		) AS tbl
			
	return @listStr	

END

GO



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_CONSULTA_BuscarServicio]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_CONSULTA_BuscarServicio]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_CONSULTA_GetActividadesByServicioId]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_CONSULTA_GetActividadesByServicioId]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_CONSULTA_GetTramiteDetails]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_CONSULTA_GetTramiteDetails]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Jose Carlos Gutierrez
-- Create date: 23/06/2012
-- Description:	Consulta si un si un servicio corresponde a los parametros enviados
-- =============================================
CREATE PROCEDURE [dbo].[usp_CONSULTA_BuscarServicio]
	@varCodigoServicio	VARCHAR(20),
	@varDocumento		VARCHAR(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
    SELECT s.[servicioId]
      ,s.[tipoServicio]
	FROM [dbo].[tbl_Servicio] s
		JOIN [dbo].[tbl_Involucrado] i ON i.[servicioId] = s.[servicioId]
		JOIN 
			(
				SELECT [personaNaturalId] AS [personaId],
					[cedula] AS [documento]
				FROM [dbo].[tbl_PersonaNatural] pn JOIN 
					[tbl_Persona] p ON p.[personaId] = pn.[personaNaturalId]
				UNION
				SELECT [personaJuridicaId] AS [personaId],
					[nit] AS [documento]
				FROM [dbo].[tbl_PersonaJuridica] pj JOIN 
					[tbl_Persona] p ON p.[personaId] = pj.[personaJuridicaId]
			) persona ON persona.[personaId] = i.[personaId]
	WHERE REPLACE([codigoServicio],' ','') = REPLACE(@varCodigoServicio,' ','')
		AND persona.[documento] = @varDocumento
END

GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Jose Carlos Gutierrez
-- Create date: 23/03/2012
-- Description:	Devuelve las actividades de un servicio
-- =============================================
CREATE PROCEDURE [dbo].[usp_CONSULTA_GetActividadesByServicioId]
	@intServicioId	INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	SELECT ROW_NUMBER() OVER(ORDER BY [fechaActividad] ASC) AS [rowNumber]
		  ,[fechaActividad]
		  ,[descripcion]
	FROM [dbo].[tbl_Actividad]
	WHERE [servicioId] = @intServicioId
	ORDER BY [fechaActividad] ASC

    
END

GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Jose Carlos Gutierrez
-- Create date: 23/06/2012
-- Description:	Devuelve el detalle de un tramite
-- =============================================
CREATE PROCEDURE [dbo].[usp_CONSULTA_GetTramiteDetails]
	@intTramiteId INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT s.[servicioId]
      ,s.[tipoServicio]
      ,s.[codigoServicio]
      ,s.[fechaInicio]
      ,s.[fechaFin]
      ,s.[fechaEntregaEstimada]
      ,s.[observacion]
      ,s.[estado]
      ,s.[fechaEntrega]
      ,s.[entregadoA]
      ,s.[observacionEntrega]
      ,cat.[descripcion] + ' - ' + tipo.[descripcion] AS [tipoTramite]
      ,[dbo].[uf_GetInvolucradosList](s.[servicioId]) AS [involucrados]
      ,[dbo].[uf_GetResponsablesList](s.[servicioId]) AS [responsables]
	FROM [dbo].[tbl_Servicio] s
		JOIN [dbo].[tbl_Tramite] t ON t.[tramiteId] = s.[servicioId]
		JOIN [dbo].[tbl_TipoTramite] tipo ON tipo.[tipoTramiteId] = t.[tipoTramiteId]
		JOIN [dbo].[tbl_CategoriaTramite] cat ON cat.[categoriaTramiteId] = tipo.[categoriaTramiteId]
	WHERE s.[servicioId] = @intTramiteId
END

GO


DELETE FROM [dbo].[tbl_Version]
GO

INSERT INTO [dbo].[tbl_Version]
           ([versionMayor]
           ,[versionMenor]
           ,[release])
     VALUES
           (1
           ,3
           ,0)
GO